﻿using System;
using System.Data;
using System.Linq;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.SqlClient;
using Common;
using Model.Sys;

namespace DAL
{
    public class RoleDAL : DB
    {
        #region 常量

        private readonly static string ROLE_TABLE_NAME = "SysRoleTable";
        private readonly static string ROLEDB_TABLE_NAME = "SysRoleDBTable";

        #endregion

        #region 公共属性

        #endregion

        #region 构造函数
        public RoleDAL(LoginInfo info)
        {
            //base.loginInfo = info;
            base.loginInfo = new LoginInfo();
        }

        #endregion

        #region 公共函数

        #region Role

        public RoleInfo GetInfoById(string roleID)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = string.Format(@"SELECT * FROM [dbo].[{0}] WHERE Id=@Id", ROLE_TABLE_NAME);
                IDataParameter[] cmdParms = { new SqlParameter("@Id", roleID) };
                DataTable dts = cmd.GetDataTable(strSql, cmdParms);
                var list = dts.MapToList<RoleInfo>();
                if (list != null && list.Count > 0)
                {
                    var info = list[0];
                    return info;
                }
            }
            catch (Exception ex)
            {
                Logger.LogError(string.Format("[dbo].[{0}]", ROLE_TABLE_NAME), "GetInfoById", 0, 1, ex, "", "");
            }
            return null;
        }

        public List<RoleDBInfo> GetUserRole(string userName, string dbName)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"select * from  [dbo].[SysRoleDBTable] rd inner join [dbo].[SysRoleTable] rt on
rd.RoleId=rt.Id where rt.Status=1 and rd.RoleID  in 
(select RoleID from  [dbo].[RoleUser] where UserCode=@userName) 
and DBId in (select id from Working_db.DBDeploy where DBName=@dbName)";
                IDataParameter[] cmdParms = { new SqlParameter("@userName", userName), new SqlParameter("@dbName", dbName) };
                DataTable dts = cmd.GetDataTable(strSql, cmdParms);
                var list = dts.MapToList<RoleDBInfo>();
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int GetUserDBConn(string userName, string dbName)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = "select count(*) from [Working_db].[UserDBConnType] where UserName=@userName and DBName=@dbName";
                IDataParameter[] cmdParms = { new SqlParameter("@userName", userName), new SqlParameter("@dbName", dbName) };
                DataTable dts = cmd.GetDataTable(strSql, cmdParms);
                if (dts != null && dts.Rows.Count > 0)
                    return Convert.ToInt32(dts.Rows[0][0]);
                else
                    return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public List<RoleInfo> GetList(string roleName, string status
    , string expIDs
    , string orderBy, bool asc
    , int pageIndex, int pageSize, out int recordCount)
        {
            recordCount = 0;
            List<RoleInfo> result = new List<RoleInfo>();
            try
            {
                HIDB.dbo cmd = base.conn();
                List<SqlParameter> cmdParms = new List<SqlParameter>();
                string strSql = string.Format(@"SELECT * FROM [dbo].[{0}]
WHERE 1=1", ROLE_TABLE_NAME);
                if (!string.IsNullOrEmpty(roleName))
                {
                    strSql += " AND Name like '%'+@Name+'%'";
                    cmdParms.Add(new SqlParameter("@Name", roleName));
                }
                if (!string.IsNullOrEmpty(status))
                {
                    strSql += " AND Status=@Status";
                    cmdParms.Add(new SqlParameter("@Status", status));
                }
                if (!string.IsNullOrEmpty(expIDs))
                {
                    //strSql += " AND Id not in (@ExpIDs)";
                    //cmdParms.Add(new SqlParameter("@ExpIDs", expIDs));
                    var expParams = expIDs.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                    strSql += " AND Id not in (";
                    for (int i = 0; i < expParams.Length; i++)
                    {
                        var param = expParams[i];
                        if (i > 0)
                        {
                            strSql += string.Format(",@P{0}", i);
                        }
                        else
                        {
                            strSql += string.Format("@P{0}", i);
                        }
                        cmdParms.Add(new SqlParameter(string.Format("@P{0}", i), param));
                    }
                    strSql += ")";
                }

                DataTable dt = cmd.GetDataTable(strSql, cmdParms.ToArray());
                result = dt.MapToList<RoleInfo>();
                recordCount = result.Count;
                if (orderBy.Equals("Name", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (asc == true)
                    {
                        result = result.OrderBy(p => p.Name).ToList();
                    }
                    else
                    {
                        result = result.OrderByDescending(p => p.Name).ToList();
                    }
                }
                else if (orderBy.Equals("RoleType", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (asc == true)
                    {
                        result = result.OrderBy(p => p.RoleType).ToList();
                    }
                    else
                    {
                        result = result.OrderByDescending(p => p.RoleType).ToList();
                    }
                }
                else if (orderBy.Equals("Status", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (asc == true)
                    {
                        result = result.OrderBy(p => p.Status).ToList();
                    }
                    else
                    {
                        result = result.OrderByDescending(p => p.Status).ToList();
                    }
                }
                else if (orderBy.Equals("Description", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (asc == true)
                    {
                        result = result.OrderBy(p => p.Description).ToList();
                    }
                    else
                    {
                        result = result.OrderByDescending(p => p.Description).ToList();
                    }
                }
                if (pageIndex > -1 && pageSize > -1)
                {
                    result = result.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                }
            }
            catch (Exception ex)
            {
                Logger.LogError(string.Format("[dbo].[{0}]", ROLE_TABLE_NAME), "GetList", 0, 1, ex, "", "");
            }
            return result;
        }

        public List<RoleInfo> GetByNameWithOutId(string roleName, string guid = null)
        {
            List<RoleInfo> result = new List<RoleInfo>();
            try
            {
                string strSql = string.Format(@"SELECT * FROM [dbo].[{0}]
  WHERE 1=1", ROLE_TABLE_NAME);
                List<IDataParameter> cmdParms = new List<IDataParameter>();

                strSql += " AND [Name] =@Name";
                cmdParms.Add(new SqlParameter("@Name", roleName));
                if (!string.IsNullOrEmpty(guid))
                {
                    strSql += " AND Id!=@Id";
                    cmdParms.Add(new SqlParameter("@Id", guid));
                }
                HIDB.dbo cmd = base.conn();
                DataTable dt = cmd.GetDataTable(strSql, cmdParms.ToArray());
                result = dt.MapToList<RoleInfo>();
            }
            catch (Exception ex)
            {
                Logger.LogError(string.Format("[dbo].[{0}]", ROLE_TABLE_NAME), "GetByNameWithOutId", 0, 1, ex, "", "");
            }
            return result;

        }

        public int Insert(RoleInfo roleInfo)
        {
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                string strSql = string.Format(@"INSERT INTO [dbo].[{0}]
           ([Id]
           ,[Name]
           ,[RoleType]
           ,[Status]
           ,[Description]
           ,[CreateTime])
     VALUES (@Id
           ,@Name
           ,@RoleType
           ,@Status
           ,@Description
           ,GETDATE())", ROLE_TABLE_NAME);
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@Id",roleInfo.Id)
                     ,new SqlParameter("@Name",roleInfo.Name)
                     ,new SqlParameter("@RoleType",roleInfo.RoleType)
                     ,new SqlParameter("@Status",roleInfo.Status??string.Empty)
                     ,new SqlParameter("@Description",roleInfo.Description??string.Empty)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError(string.Format("[dbo].[{0}]", ROLE_TABLE_NAME), "Insert", 0, 1, ex, "", "");
            }
            return num;
        }

        public int Update(RoleInfo roleInfo)
        {
            int num = -1;

            //创建cmd对象
            HIDB.dbo cmd = base.conn();

            //执行命令返回结果
            try
            {
                string strSql = string.Format(@"UPDATE [dbo].[{0}]
   SET [Name] = @Name
      ,[RoleType] = @RoleType
      ,[Status] = @Status
      ,[Description] = @Description
 WHERE [Id] = @Id", ROLE_TABLE_NAME);
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@Id",roleInfo.Id)
                     ,new SqlParameter("@Name",roleInfo.Name)
                     ,new SqlParameter("@RoleType",roleInfo.RoleType)
                     ,new SqlParameter("@Status",roleInfo.Status??string.Empty)
                     ,new SqlParameter("@Description",roleInfo.Description??string.Empty)
                    
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError(string.Format("[dbo].[{0}]", ROLE_TABLE_NAME), "Update", 0, 1, ex, "", "");
            }
            return num;
        }

        public int Delete(string roleID)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = string.Format(@"DELETE FROM [dbo].[{0}] WHERE [Id]=@Id;
DELETE FROM [dbo].[{1}] WHERE [RoleId]=@Id", ROLE_TABLE_NAME, ROLEDB_TABLE_NAME);
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@Id",roleID)
                };
                cmd.ExecuteSql(strSql, cmdParms);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region DB

        public List<DBInfo> GetAllDB()
        {
            List<DBInfo> result = new List<DBInfo>();
            try
            {
                string strSql = string.Format(@"SELECT * FROM {0}", "[Working_db].[DBDeploy]");
                List<IDataParameter> cmdParms = new List<IDataParameter>();
                HIDB.dbo cmd = base.conn();
                DataTable dt = cmd.GetDataTable(strSql, cmdParms.ToArray());
                result = dt.MapToList<DBInfo>();
            }
            catch (Exception ex)
            {
                Logger.LogError("[Working_db].[DBDeploy]", "GetAllDB", 0, 1, ex, "", "");
            }
            return result;

        }


        #endregion

        #region RoleDB

        public List<RoleDBInfo> GetRoleDBByRoleID(string roleId)
        {
            List<RoleDBInfo> result = new List<RoleDBInfo>();
            try
            {
                string strSql = string.Format(@"SELECT * FROM [dbo].[{0}]
  WHERE 1=1", ROLEDB_TABLE_NAME);
                List<IDataParameter> cmdParms = new List<IDataParameter>();

                strSql += " AND [RoleId] =@RoleId";
                cmdParms.Add(new SqlParameter("@RoleId", roleId));
                HIDB.dbo cmd = base.conn();
                DataTable dt = cmd.GetDataTable(strSql, cmdParms.ToArray());
                result = dt.MapToList<RoleDBInfo>();
            }
            catch (Exception ex)
            {
                Logger.LogError(string.Format("[dbo].[{0}]", ROLEDB_TABLE_NAME), "GetRoleDBByRoleID", 0, 1, ex, "", "");
            }
            return result;

        }

        public int DeleteRoleDB(string roleId, int dbId)
        {
            int num = -1;
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = string.Format(@"DELETE FROM [dbo].[{0}]
 WHERE [roleId]=@roleId AND [DBId]=@DBId", ROLEDB_TABLE_NAME);
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@roleId",roleId),
                    new SqlParameter("@DBId",dbId)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError(string.Format("[dbo].[{0}]", ROLEDB_TABLE_NAME), "DeleteRoleDB", 0, 1, ex, "", "");
            }
            return num;
        }

        public int UpdateRoleDB(RoleDBInfo roledbInfo)
        {
            int num = -1;

            //创建cmd对象
            HIDB.dbo cmd = base.conn();

            //执行命令返回结果
            try
            {
                string strSql = string.Format(@"UPDATE [dbo].[{0}]
   SET [IsConnected] = @IsConnected
      ,[IsEnable] = @IsEnable
      ,[IsManagement] = @IsManagement
      ,[IsView] = @IsView
 WHERE [RoleId]=@RoleId AND [DBId]=@DBId", ROLEDB_TABLE_NAME);
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@RoleId",roledbInfo.RoleId)
                     ,new SqlParameter("@DBId",roledbInfo.DBId)
                     ,new SqlParameter("@IsConnected",roledbInfo.IsConnected)
                     ,new SqlParameter("@IsEnable",roledbInfo.IsEnable)
                     ,new SqlParameter("@IsManagement",roledbInfo.IsManagement)
                     ,new SqlParameter("@IsView",roledbInfo.IsView)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError(string.Format("[dbo].[{0}]", ROLEDB_TABLE_NAME), "Update", 0, 1, ex, "", "");
            }
            return num;
        }

        public int InsertRoleDB(RoleDBInfo roledbInfo)
        {
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                string strSql = string.Format(@"INSERT INTO [dbo].[{0}]
           ([RoleId]
           ,[DBId]
           ,[IsConnected]
           ,[IsEnable]
           ,[IsManagement]
           ,[IsView])
     VALUES (@RoleId
           ,@DBId
           ,@IsConnected
           ,@IsEnable
           ,@IsManagement
           ,@IsView)", ROLEDB_TABLE_NAME);
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@RoleId",roledbInfo.RoleId)
                     ,new SqlParameter("@DBId",roledbInfo.DBId)
                     ,new SqlParameter("@IsConnected",roledbInfo.IsConnected)
                     ,new SqlParameter("@IsEnable",roledbInfo.IsEnable)
                     ,new SqlParameter("@IsManagement",roledbInfo.IsManagement)
                     ,new SqlParameter("@IsView",roledbInfo.IsView)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError(string.Format("[dbo].[{0}]", ROLEDB_TABLE_NAME), "Insert", 0, 1, ex, "", "");
            }
            return num;
        }

        #endregion

        #region 组内用户

        public List<RoleUserInfo> GetUserByRoleID(string roleId, string userName, string employeeName
            , string orderBy, bool asc
            , int pageIndex, int pageSize, out int recordCount)
        {
            List<RoleUserInfo> result = new List<RoleUserInfo>();
            recordCount = 0;
            try
            {
                string strSql = string.Format(@"SELECT RoleUser.Guid,RoleUser.RoleID,RoleUser.UserStatus 
,SysUser.EmployeeCode,SysUser.EmployeeName,SysUser.Username,SysUser.OfficeEmail,SysUser.OfficeTel
FROM RoleUser INNER JOIN SysUser  ON RoleUser.UserCode=SysUser.Username 
WHERE 1=1");
                List<IDataParameter> cmdParms = new List<IDataParameter>();


                strSql += " AND RoleUser.RoleId =@RoleId";
                cmdParms.Add(new SqlParameter("@RoleId", roleId));

                if (!string.IsNullOrEmpty(userName))
                {
                    strSql += " AND SysUser.Username=@Username";
                    cmdParms.Add(new SqlParameter("@Username", userName));
                }
                if (!string.IsNullOrEmpty(employeeName))
                {
                    strSql += " AND (SysUser.EmployeeName like '%'+@EmployeeName+'%')";
                    cmdParms.Add(new SqlParameter("@EmployeeName", employeeName));
                }

                HIDB.dbo cmd = base.conn();
                DataTable dt = cmd.GetDataTable(strSql, cmdParms.ToArray());
                result = dt.MapToList<RoleUserInfo>();
                recordCount = result.Count;
                if (orderBy.Equals("UserName", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (asc == true)
                    {
                        result = result.OrderBy(p => p.UserName).ToList();
                    }
                    else
                    {
                        result = result.OrderByDescending(p => p.UserName).ToList();
                    }
                }
                else if (orderBy.Equals("UserStatus", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (asc == true)
                    {
                        result = result.OrderBy(p => p.UserStatus).ToList();
                    }
                    else
                    {
                        result = result.OrderByDescending(p => p.UserStatus).ToList();
                    }
                }
                else if (orderBy.Equals("EmployeeName", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (asc == true)
                    {
                        result = result.OrderBy(p => p.EmployeeName).ToList();
                    }
                    else
                    {
                        result = result.OrderByDescending(p => p.EmployeeName).ToList();
                    }
                }
                else if (orderBy.Equals("CreateTime", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (asc == true)
                    {
                        result = result.OrderBy(p => p.CreateTime).ToList();
                    }
                    else
                    {
                        result = result.OrderByDescending(p => p.CreateTime).ToList();
                    }
                }
                if (pageIndex > -1 && pageSize > -1)
                {
                    result = result.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                }
            }
            catch (Exception ex)
            {
                Logger.LogError(string.Format("[dbo].[{0}]", ROLEDB_TABLE_NAME), "GetRoleDBByRoleID", 0, 1, ex, "", "");
            }
            return result;

        }
        /// <summary>
        /// 验证组内用户是否存在
        /// </summary>
        /// <param name="roleName"></param>
        /// <param name="userName"></param>
        /// <returns></returns>
        public bool GetUserByRoleName(string roleName, string userName)
        {
            try
            {
                string strSql = string.Format(@"select 1 from  [dbo].[SysRoleTable] rt inner join [dbo].RoleUser ru on
rt.Id=ru.RoleID where ru.UserCode=@userName and rt.Name=@roleName");
                List<SqlParameter> cmdParms = new List<SqlParameter>();

                cmdParms.Add(new SqlParameter("@roleName", roleName));
                cmdParms.Add(new SqlParameter("@userName", userName));

                HIDB.dbo cmd = base.conn();
                DataTable dt = cmd.GetDataTable(strSql, cmdParms.ToArray());
                if (dt != null && dt.Rows.Count > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int InsertRoleUser(RoleUserInfo roleUserInfo)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = string.Format(@"IF NOT EXISTS(SELECT 1 FROM [RoleUser] WHERE [RoleID]=@RoleID AND [UserCode]=@UserCode)
BEGIN
	INSERT INTO [dbo].[RoleUser]([Guid],[RoleID],[UserName],[UserCode],[CreateUser],[CreateTime])
     VALUES (@Guid,@RoleID,@UserName,@UserCode,@CreateUser,GETDATE())
END");
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@Guid",roleUserInfo.Guid)
                     ,new SqlParameter("@RoleID",roleUserInfo.RoleID)
                     ,new SqlParameter("@UserName",roleUserInfo.EmployeeName)
                     ,new SqlParameter("@UserCode",roleUserInfo.UserName)
                     ,new SqlParameter("@CreateUser",roleUserInfo.CreateUser??"")
                };
                cmd.ExecuteSql(strSql, cmdParms);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int DeleteRoleUser(string guid)
        {
            int num = -1;
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = string.Format(@"DELETE FROM [dbo].[{0}] WHERE [Guid]=@Id", "RoleUser");
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@Id",guid)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError(string.Format("[dbo].[{0}]", "RoleUser"), "Delete", 0, 1, ex, "", "");
            }
            return num;
        }

        #endregion

        #region SysUser

        public SysUserInfo GetUserByUsername(string username)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = string.Format(@"SELECT * FROM [dbo].[{0}] WHERE Username=@Username", "SysUser");
                IDataParameter[] cmdParms = { new SqlParameter("@Username", username) };
                DataTable dts = cmd.GetDataTable(strSql, cmdParms);
                var list = dts.MapToList<SysUserInfo>();
                if (list != null && list.Count > 0)
                {
                    var info = list[0];
                    return info;
                }
            }
            catch (Exception ex)
            {
                Logger.LogError(string.Format("[dbo].[{0}]", "SysUser"), "GetInfoById", 0, 1, ex, "", "");
            }
            return null;
        }

        public bool UpUserStatus(string guid, string status)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"update RoleUser set UserStatus=@status where Guid=@guid";
                IDataParameter[] cmdParms = { new SqlParameter("@guid", guid), new SqlParameter("@status", status) };
                cmd.GetDataTable(strSql, cmdParms);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        #endregion

        #endregion

    }
}
